﻿/*----------------------------------------------------------------
// Copyright (C) 2011 上海
// 版权所有。 
//
// 文件名：DataAccessFundsOperatingSummaryReport
// 文件功能描述：增减款汇总报表数据访问层
//
// 
// 创建标识：2012-03-20 王冲
//
// 修改标识：
// 修改描述：
//
//
//----------------------------------------------------------------*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DbAdapter.sqldbAdapter;
using System.Data;
using System.Data.SqlClient;

namespace DAO.WaiGaoQiao
{
    public class DataAccessFundsOperatingSummaryReport
    {
        /// <summary>
        /// 获取部门列表（用于绑定下拉框）
        /// </summary>
        /// <returns></returns>
        public DataSet GetdeptName()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select Dep_ID,deptName from DeptView order by Dep_ID";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }

        /// <summary>
        /// 获取增减款汇总表
        /// </summary>
        /// <returns></returns>
        public DataSet BindList(int pagesize, int startindex, object[] obj, string tablename, out int count)
        {
            /*
             * 先调用sp_CreateTabelForDeposith存储过程创建一个表名为tablename的表,用来存储汇总数据
             */
            sqldb db = new sqldb();
            SqlParameter[] parameters = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30)
                                        };
            parameters[0].Value = tablename;
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_CreateTabelForDeposith", parameters);

            /*
             * 再从开始日期循环到结束日期，调用SP_CONREPDEPOSITFORDATE存储过程向刚才新建的表里面逐一的分析出数据添加到表中,每天一条。
             */
            for (DateTime de = Convert.ToDateTime(obj[0]); de <= Convert.ToDateTime(obj[1]); de = de.AddDays(1))
            {
                SqlParameter[] parameters1 = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30),
                                            new SqlParameter("@CURDATA", SqlDbType.DateTime),
                                            new SqlParameter("@DEPTID", SqlDbType.VarChar, 5000),
                                            new SqlParameter("@PERSONELID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@PERSONNELTYPEID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NAME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NODEID", SqlDbType.VarChar, 50),
                                            new SqlParameter("@MACNO", SqlDbType.VarChar, 100),
                                            new SqlParameter("@DepositName", SqlDbType.VarChar, 20)
                                        };
                parameters1[0].Value = tablename;
                parameters1[1].Value = de;
                parameters1[2].Value = obj[2];
                parameters1[3].Value = obj[3];
                parameters1[4].Value = "%";
                parameters1[5].Value = obj[4];
                parameters1[6].Value = "%";
                parameters1[7].Value = "%";
                parameters1[8].Value = obj[5];
                db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "SP_CONREPDEPOSITFORDATE", parameters1);
            }
            /*
             * 获取刚创建的表中的记录数并用out返回用于分页
             */
            string query = "select count(1) from " + tablename;
            object rowcount = db.ExecuteScalar(GetConntionString.conntionstring(), CommandType.Text, query);
            count = Convert.ToInt32(rowcount);

            /*
             * 从刚才创建的表中取出汇总数据，然后分页，根据日期排序
             */
            int startrow = pagesize * (startindex - 1);
            int endrow = pagesize * startindex + 1;
            query = "select * from (select ROW_NUMBER() OVER(ORDER BY ITEMCODE DESC) rownumber,* " +
                           "from " + tablename + " where 1=1 ) B "
                         + "where B.rownumber > " + startrow.ToString() + " and B.rownumber < " + endrow.ToString();
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }

        public DataSet GetListAll(object[] obj, string tablename)
        {
            /*
             * 先调用sp_CreateTabelForDeposith存储过程创建一个表名为tablename的表,用来存储汇总数据
             */
            sqldb db = new sqldb();
            SqlParameter[] parameters = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30)
                                        };
            parameters[0].Value = tablename;
            db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "sp_CreateTabelForDeposith", parameters);

            /*
             * 再从开始日期循环到结束日期，调用SP_CONREPDEPOSITFORDATE存储过程向刚才新建的表里面逐一的分析出数据添加到表中,每天一条。
             */
            for (DateTime de = Convert.ToDateTime(obj[0]); de <= Convert.ToDateTime(obj[1]); de = de.AddDays(1))
            {
                SqlParameter[] parameters1 = {
                                            new SqlParameter("@TEMPTABLENAME", SqlDbType.VarChar, 30),
                                            new SqlParameter("@CURDATA", SqlDbType.DateTime),
                                            new SqlParameter("@DEPTID", SqlDbType.VarChar, 5000),
                                            new SqlParameter("@PERSONELID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@PERSONNELTYPEID", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NAME", SqlDbType.VarChar, 20),
                                            new SqlParameter("@NODEID", SqlDbType.VarChar, 50),
                                            new SqlParameter("@MACNO", SqlDbType.VarChar, 100),
                                            new SqlParameter("@DepositName", SqlDbType.VarChar, 20)
                                        };
                parameters1[0].Value = tablename;
                parameters1[1].Value = de;
                parameters1[2].Value = obj[2];
                parameters1[3].Value = obj[3];
                parameters1[4].Value = "%";
                parameters1[5].Value = obj[4];
                parameters1[6].Value = "%";
                parameters1[7].Value = "%";
                parameters1[8].Value = obj[5];
                db.ExecuteNonQuery(GetConntionString.conntionstring(), CommandType.StoredProcedure, "SP_CONREPDEPOSITFORDATE", parameters1);
            }

            /*
             * 从刚才创建的表中取出汇总数据，根据日期排序
             */
            string query = "select * from " + tablename + " order by ITEMCODE DESC";
            DataSet ds = db.ExecuteDataset(GetConntionString.conntionstring(), CommandType.Text, query);
            return ds;
        }

        /// <summary>
        /// 获取增减名称列表（用于绑定下拉框）
        /// </summary>
        /// <returns></returns>
        public DataSet GetDepositTypeID()
        {
            using (SqlConnection conn = new SqlConnection(GetConntionString.conntionstring()))
            {
                sqldb db = new sqldb();
                string query = "select DepositTypeID,DepositName from Con_DepositType where Account=1 or Account=0 and bUse=1 order by DepositTypeID";
                DataSet ds = db.ExecuteDataset(conn, CommandType.Text, query);
                return ds;
            }
        }
    }
}
